#!/usr/bin/python
# coding:utf-8

'''
    database operation module
    Usually, you don't need to use this(db.py),you may use orm.py which decorates db.py

    usage:

    first, you should create the engine (must, but only once) like:
        db.create_engine(user='user', password='pwd', database='test_db')

    then, execute any sql use update():
        db.update('drop table if exists user')
        db.update('create table user (\
            id int not null primary key auto_increment,\
            name text not null,\
            passwd text not null,\
            email text,\
            last_modified real not null)')

        #db.update('select * from user where email =?', 'notexist@test.org') returns 0
        #db.update('drop ? if exists ?', 'table', 'user') is wrong syntax, 
        #becauce table and user will be auto surround with ''(number will not be surrounded),
        #the sql will be      drop 'table' if exists 'user'
        #you can use ?? instand of ? , like :
            #db.update('drop ?? if exists ??', 'table', 'user')
        #which will not surround with ''
            #=.= allright,my fault
            #db.update('drop %s if exists %s' % ('table','user')) brings the same effect

    and, insert some data:
        u1 = db.Dict(name='chenyan', email='chenyanclyz@163.com',
               passwd='123', last_modified=time.time())
        db.insert('user', **u1)

    and, select some data:
        u1 = db.select_one('select * from user where id = ?', 1)
        print u1.name
        print u1.email

        l1 = db.select('select * from user where name = ?', "chenyan")
        for u in l1:
            print u.id

        #print how many user use the email address like 'notexist@test.org'
        print db.select_int('select count(*) from user where email=?', 'notexist@test.org')
        print db.update('select * from user where email=?', 'notexist@test.org')

    and, if you wante to delete some data ,we suggest you to use update() instand.

'''

import logging
import threading
import functools
import uuid 
import time

from _dict import Dict

logging.basicConfig(level=logging.DEBUG)

def next_id(t=None):
    '''
    in orm.py we use this method to create uniq id, 
    we do not use something like 'auto_increment' in mysql ,
    everything need control will be do in orm.py,

    '''
    if t is None:
        t = time.time()
    return '%015d%s000' % (int(t * 1000), uuid.uuid4().hex)


# def _profiling(start, sql=''):
#     '''log the performance of database operations '''
#     t = time.time() - start
#     if t > 0.1:
#         logging.warning('[PROFILING] [DB] %s: %s' % (t, sql))
#     else:
#         logging.info('[PROFILING] [DB] %s: %s' % (t, sql))


class _Engine(object):

    """database engine,also called the connection to the database"""

    def __init__(self, connect):
        self._connect = connect

    def connect(self):
        return self._connect()

engine = None


class _LasyConnection(object):

    def __init__(self):
        self.connection = None

    def cursor(self):
        if self.connection is None:
            connection = engine.connect()
            logging.info('open connection <%s>...' % hex(id(connection)))
            self.connection = connection
        return self.connection.cursor()

    def commit(self):
        self.connection.commit()

    def rollback(self):
        self.connection.rollback()

    def cleanup(self):
        if self.connection:
            connection = self.connection
            self.connection = None
            logging.info('close connection <%s>...' % hex(id(connection)))
            connection.close()


class _DbCtx(threading.local):

    """context which has database connection"""

    def __init__(self):
        self.connection = None
        self.transactions = 0

    def is_init(self):
        return not self.connection is None

    def init(self):
        self.connection = _LasyConnection()
        self.transactions = 0

    def cleanup(self):
        self.connection.cleanup()
        self.connection = None

    def cursor(self):
        return self.connection.cursor()

_db_ctx = _DbCtx()


class _ConnectionCtx(object):

    ''' __enter__ and __exit__, connections can be auto init and cleanup'''

    def __enter__(self):
        global _db_ctx
        self.should_cleanup = False
        if not _db_ctx.is_init():
            _db_ctx.init()
            self.should_cleanup = True
        return self

    def __exit__(self, exctype, excvalue, traceback):
        global _db_ctx
        if self.should_cleanup:
            _db_ctx.cleanup()


# def connection():
#     return _ConnectionCtx()


def with_connection(func):
    '''
    Decorator for reuse connection.
    '''
    @functools.wraps(func)
    def _wrapper(*args, **kw):
        with _ConnectionCtx():
            return func(*args, **kw)
    return _wrapper


def create_engine(user, password, database, host='127.0.0.1', port=3306, **kw):
    import mysql.connector as mysql
    global engine
    if engine is not None:
        raise DBError('Engine is already initialized.')
    params = dict(
        user=user, password=password, database=database, host=host, port=port)
    defaults = dict(use_unicode=True, charset='utf8',
                    collation='utf8_general_ci', autocommit=False)
    for k, v in defaults.iteritems():
        params[k] = kw.pop(k, v)
    params.update(kw)
    params['buffered'] = True
    engine = _Engine(lambda: mysql.connect(**params))
    # test connection...
    logging.info('Init mysql engine <%s> ok.' % hex(id(engine)))


def _select(sql, first, *args):
    '''execute select SQL and return unique result or list results.'''
    global _db_ctx
    cursor = None
    split = len(sql.split('??')) - 1
    sql = sql.replace('??', '%s')
    sql = sql % args[:split]
    sql = sql.replace('?', '%s')
    logging.info('SQL: %s, ARGS: %s' % (sql, args[split:]))
    try:
        cursor = _db_ctx.connection.cursor()
        cursor.execute(sql, args[split:])
        if cursor.description:
            names = [x[0] for x in cursor.description]
        if first:
            values = cursor.fetchone()
            if not values:
                return None
            return Dict(names, values)
        return [Dict(names, x) for x in cursor.fetchall()]
    finally:
        if cursor:
            cursor.close()


@with_connection
def select(sql, *args):
    '''
    return a list ,each element in the list is a db.Dict. 
    each Dict represent one line in the select reslut.

    if no reslut found return a empty list
    '''
    return _select(sql, False, *args)


@with_connection
def select_one(sql, *args):
    '''
    Execute select SQL and expected one result.
    If no result found, return None.
    If multiple results found, the first one returned.
    '''
    return _select(sql, True, *args)


@with_connection
def select_int(sql, *args):
    '''
    Execute select SQL and expected one int and only one int result.
    >>> n = update('delete from user')
    >>> u1 = dict(id=96900, name='Ada', email='ada@test.org', passwd='A-12345', last_modified=time.time())
    >>> u2 = dict(id=96901, name='Adam', email='adam@test.org', passwd='A-12345', last_modified=time.time())
    >>> insert('user', **u1)
    1
    >>> insert('user', **u2)
    1
    >>> select_int('select count(*) from user')
    2
    >>> select_int('select count(*) from user where email=?', 'ada@test.org')
    1
    >>> select_int('select count(*) from user where email=?', 'notexist@test.org')
    0
    >>> select_int('select id from user where email=?', 'ada@test.org')
    96900
    >>> select_int('select id, name from user where email=?', 'ada@test.org')
    Traceback (most recent call last):
    ...
    MultiColumnsError: Expect only one column.
    '''
    d = _select(sql, True, *args)
    if len(d) != 1:
        raise MultiColumnsError('Expect only one column.')
    return d.values()[0]


def _update(sql, *args):
    global _db_ctx
    cursor = None
    split = len(sql.split('??')) - 1
    sql = sql.replace('??', '%s')
    sql = sql % args[:split]
    sql = sql.replace('?', '%s')
    logging.info('SQL: %s, ARGS: %s' % (sql, args[split:]))
    try:
        cursor = _db_ctx.connection.cursor()
        cursor.execute(sql, args[split:])
        r = cursor.rowcount
        if _db_ctx.transactions == 0:
            # no transaction enviroment:
            logging.info('auto commit')
            _db_ctx.connection.commit()
        return r
    except Exception,e:
        raise DBError(e)
    finally:
        if cursor:
            cursor.close()


@with_connection
def update(sql, *args):
    '''return the number of effect raws'''
    return _update(sql, *args)


@with_connection
def insert(table, **kw):
    '''return the number of effect raws'''
    ks, ws = zip(*kw.iteritems())
    vs = list()
    for i in range(len(ks)):
        vs.append('?')
    sql = 'insert into %s (%s) values (%s)' % (
        table, ",".join(ks), ",".join(vs))
    return _update(sql, *ws)

# @with_connection
# def delete(table, **kw):
#     sql = 

class DBError(Exception):
    pass


class MultiColumnsError(DBError):
    pass
